﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using EAF.Utility;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace App.Utility
{
    public class DoExcel
    {
        #region Excel

        #region 生成

        /// <summary>
        /// 将DataTable数据导入到内存流中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称 如sheet1</param>
        /// <param name="fileName">下载文件名 如excel1.xlsx</param>
        /// <param name="ms">内存流</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static int DataTable2ExcelStream(DataTable data, string sheetName, bool isColumnWritten, string fileName, MemoryStream ms)
        {
            if (data == null)
            {
                throw new ArgumentNullException("data");
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new ArgumentNullException(sheetName);
            }
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }


            //创建excel实体
            IWorkbook workbook = null;
            if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
            {
                workbook = new XSSFWorkbook();
            }
            else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
            {
                workbook = new HSSFWorkbook();
            }

            try
            {
                ISheet sheet;
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                int j;
                int count;
                //写入DataTable的列名，写入单元格中
                if (isColumnWritten)
                {
                    var row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }
                //遍历循环datatable具体数据项
                int i;
                for (i = 0; i < data.Rows.Count; ++i)
                {
                    var row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }

                //将excel写出到内存流
                workbook.Write(ms);

                return count;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

        /// <summary>
        /// 将DataTable数据导入到excel字节数组
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称 sheet1</param>
        /// <param name="fileName">下载文件名 excel1.xlsx</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public static byte[] DataTableToExcelByte(DataTable data, string sheetName, bool isColumnWritten, string fileName)
        {
            MemoryStream ms = new MemoryStream();
            DataTable2ExcelStream(data, sheetName, isColumnWritten, fileName, ms);
            var byteFile = ms.ToArray();
            ms.Close();
            ms.Dispose();

            return byteFile;
        }

        /// <summary>
        /// 使用List<T>写入Excel
        /// </summary>
        public static bool Create<T>(List<T> list, string filePath, bool IsOverWrite = true, bool IsAppendTitle = true, string[] Headers = null, string fileName = "")
        {
            return Create(list.ToDataTable<T>(), filePath, IsOverWrite, IsAppendTitle, Headers, fileName);
        }

        /// <summary>
        /// 从DataTable生成Excel
        /// </summary>
        public static bool Create(DataTable dt, string filePath, bool IsOverWrite = true, bool IsAppendTitle = true, string[] Headers = null, string fileName = "")
        {
            try
            {
                dt = dt ?? new DataTable();
                if (!Directory.Exists(filePath))
                {
                    Directory.CreateDirectory(filePath);
                }
                FileInfo file = new FileInfo(DoPath.GetFullPath(filePath + fileName));
                if (file.Exists && !IsOverWrite)
                {
                    file.Delete();
                }
                //创建Excel文件的对象
                HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                //添加一个sheet
                ISheet sheet1 = book.CreateSheet("Sheet1");

                #region //书写标题
                if (IsAppendTitle)
                {
                    if (Headers == null || Headers.Length == 0) { Headers = dt.GetColumnNames().ToArray(); }

                    //给sheet1添加第一行的头部标题
                    IRow row1 = sheet1.CreateRow(0);

                    for (int i = 0; i < Headers.Length; i++)
                    {
                        row1.CreateCell(i).SetCellValue(Headers[i]);
                    }
                }
                #endregion

                #region //填充数据
                var k = IsAppendTitle ? 2 : 1;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //创建数据行
                    IRow rowtemp = sheet1.CreateRow(k);
                    rowtemp.CreateCell(0).SetCellValue((k).ToString());
                    k++;
                    //填充数据
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        var value = dt.Rows[i][j].ToString();
                        rowtemp.CreateCell(j).SetCellValue(value);
                    }
                }
                //foreach (DataRow row in dt.Rows)
                //{
                //    //创建数据行
                //    IRow rowtemp = sheet1.CreateRow(k);
                //    rowtemp.CreateCell(0).SetCellValue((k).ToString());
                //    k++;
                //    //填充数据
                //    int j = 1;
                //    foreach (var item in row.ItemArray)
                //    {
                //        rowtemp.CreateCell(j).SetCellValue(item.ToString());
                //        j++;
                //    }
                //}
                #endregion

                #region //写入文件
                using (MemoryStream ms = new MemoryStream())
                {
                    book.Write(ms);
                    ms.Seek(0, SeekOrigin.Begin);

                    using (FileStream FileStream = new FileStream(file.FullName, FileMode.Create, System.IO.FileAccess.Write))
                    {
                        byte[] bytes = new byte[ms.Length];
                        ms.Read(bytes, 0, (int)ms.Length);
                        FileStream.Write(bytes, 0, bytes.Length);
                        FileStream.Close();
                        ms.Close();
                    }
                }
                #endregion

                file.Refresh();
                return file.Exists;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        /// <summary>
        /// 保存excel文件流对象
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static bool SaveExcel(string fileUrl, Stream stream)
        {
            fileUrl = DoPath.GetFullPath(fileUrl);
            var file = new FileInfo(fileUrl);
            if (!file.Directory.Exists) { file.Directory.Create(); }
            if (file.Exists) { file.Delete(); }
            if (stream != null)
            {
                try
                {
                    byte[] Buf = new Byte[stream.Length];
                    stream.Read(Buf, 0, Buf.Length);
                    stream.Seek(0, SeekOrigin.Begin);
                    using (FileStream fs = new FileStream(fileUrl, FileMode.Create, FileAccess.Write))
                    {
                        fs.Write(Buf, 0, Buf.Length);
                        fs.Close();
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
            }
            return false;
        }

        #endregion

        #region 读取

        /// <summary>
        /// 将excel流中的数据导入到DataTable中
        /// </summary>
        /// <param name="stream">数据流</param>
        /// <param name="fileName">xxx.xlsx文件名</param>
        /// <param name="sheetName">excel工作薄sheet的名称</param> 
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public static DataTable ExcelStreamToDataTable(Stream stream, string fileName, string sheetName = "", bool isFirstRowColumn = true)
        {
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException(fileName);
            }
            var data = new DataTable();
            IWorkbook workbook = null;

            try
            {
                if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
                {
                    workbook = new XSSFWorkbook(stream);
                }
                else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0)
                {
                    workbook = new HSSFWorkbook(stream);
                }

                ISheet sheet = null;
                if (workbook != null)
                {
                    //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    if (sheetName == "")
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
                    }
                }
                if (sheet == null) return data;
                var firstRow = sheet.GetRow(0);
                //一行最后一个cell的编号 即总的列数
                int cellCount = firstRow.LastCellNum;
                int startRow;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        var cellValue = cell.StringCellValue;
                        if (cellValue == null) continue;
                        var column = new DataColumn(cellValue);
                        data.Columns.Add(column);
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }
                //最后一列的标号
                var rowCount = sheet.LastRowNum;
                for (var i = startRow; i <= rowCount; ++i)
                {
                    var row = sheet.GetRow(i);
                    //没有数据的行默认是null
                    if (row == null) continue;
                    var dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        //同理，没有数据的单元格都默认是null
                        if (row.GetCell(j) != null)
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    data.Rows.Add(dataRow);
                }

                return data;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// DataSet from Excel
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="IsReadHiddenSheet"></param>
        /// <returns></returns>
        public static DataSet Read(string FilePath, bool IsReadHiddenSheet = true)
        {
            var ds = new DataSet();
            var workbook = GetExcelWorkBook(FilePath);
            if (workbook != null)
            {
                for (var i = 0; i < workbook.NumberOfSheets; i++)
                {
                    if (!IsReadHiddenSheet && workbook.IsSheetHidden(i)) { Console.WriteLine("HiddenSheet 不读取"); continue; }

                    var dt = GetDtBySheet(workbook.GetSheetAt(i), false) ?? new DataTable();

                    ds.Tables.Add(dt);
                }
            }
            return ds;
        }

        /// <summary>
        /// DataTable from Excel
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="IsHasTitle"></param>
        /// <param name="SheetIndex"></param>
        /// <returns></returns>
        public static DataTable Read(string FilePath, bool IsHasTitle = true, int SheetIndex = 0)
        {
            var workbook = GetExcelWorkBook(FilePath);
            if (workbook != null)
            {
                return GetDtBySheet(workbook.GetSheetAt(SheetIndex), IsHasTitle);
            }

            return null;
        }

        /// <summary>
        /// 根据Excle文件链接读取Workbook并返回
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        public static IWorkbook GetExcelWorkBook(string FilePath)
        {
            var file = new FileInfo(DoPath.GetFullPath(FilePath));
            if (file.Exists)
            {
                Stream fs = null;
                try
                {
                    #region 填充文件内容到IWorkbook
                    IWorkbook workbook = null;
                    using (fs = File.OpenRead(file.FullName))
                    {
                        switch (file.Extension.ToLower())
                        {
                            case ".xls": workbook = new HSSFWorkbook(fs); break; //2003版本
                            case ".xlsx": workbook = new XSSFWorkbook(fs); break; //2007以后的版本
                                                                                  //其他文件名的不管.返回null
                        }
                    }
                    return workbook;
                    #endregion
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
                finally
                {
                    if (fs != null)
                    {
                        fs.Close();
                        fs.Dispose();
                    }
                }
            }
            return null;
        }

        /// <summary>
        /// 从Sheet中填充DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="IsHasTitle"></param>
        /// <returns></returns>
        public static DataTable GetDtBySheet(ISheet sheet, bool IsHasTitle = true)
        {

            if (sheet != null)
            {
                var dt = new DataTable();

                dt.TableName = sheet.SheetName;

                #region 为DataTable增加列
                IRow firstRow = sheet.GetRow(0);//第一行
                var ColCount = firstRow.LastCellNum;//到哪一列结束

                short FirstCellNum = firstRow.FirstCellNum; //第一行的开始列
                short LastCellNum = firstRow.LastCellNum;   //第一行的结束列
                if (IsHasTitle)
                {
                    firstRow.Cells.ForEach(cell =>
                    {
                        if (cell != null)
                        {
                            dt.Columns.Add(cell.StringCellValue ?? "Col" + cell.ColumnIndex, typeof(string));
                        }
                    });
                }
                else
                {
                    for (var i = 0; i < ColCount; i++) { dt.Columns.Add("Col" + i, typeof(string)); }
                }
                #endregion

                #region 为DataTable填充内容
                for (int i = IsHasTitle ? 1 : 0; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i); //当前CellRow行

                    var tempContext = new List<string>();

                    //LastCellNum是最后一列的后一列编号, 所以不是 <=
                    for (short ii = FirstCellNum; ii < LastCellNum; ii++)
                    {
                        var cell = row.GetCell(ii);
                        var text = string.Empty;
                        try
                        {
                            switch (cell.CellType)
                            {
                                case CellType.Numeric: text = cell.NumericCellValue.ToString(); break;
                                case CellType.Boolean: text = cell.BooleanCellValue.ToString(); break;
                                case CellType.Blank: text = string.Empty; break;
                                default: text = cell.StringCellValue; break;
                            }
                        }
                        catch { text = ""; }
                        tempContext.Add(text);
                    }
                    dt.Rows.Add(tempContext.ToArray());
                }
                #endregion

                #region 删除Excel读到数据的最后空行
                var RowCount = dt.Rows.Count;
                for (int i = RowCount - 1; i >= 0; i--)
                {
                    if (string.IsNullOrEmpty(string.Join("", dt.Rows[i].ItemArray)))
                    {
                        dt.Rows.RemoveAt(i); //删除该行
                    }
                    else
                    {
                        break;
                    }
                }
                #endregion

                return dt;
            }
            return null;
        }

        #endregion

        #endregion

        #region Csv

        //#region 使用List<T>生成CSV文件
        ///// <summary>
        ///// 使用List<T>生成CSV文件
        ///// </summary>
        //public static bool CreateCSV<T>(List<T> list, string filePath, bool IsAppendTitle = true, bool IsOverWrite = true)
        //{
        //    return CreateCSV(list.ToDataTable<T>(), filePath, IsAppendTitle, IsOverWrite);
        //}
        //#endregion

        //#region 用datatable生成CSV文件
        ///// <summary>
        ///// 用datatable生成CSV文件
        ///// </summary>
        //public static bool CreateCSV(DataTable dt, string filePath, bool IsAppendTitle = true, bool IsOverWrite = true)
        //{
        //    try
        //    {
        //        dt = dt ?? new DataTable();
        //        FileInfo file = new FileInfo(DoPath.GetFullPath(filePath));
        //        //文件存在并且不允许覆盖则返回true 否则删除文件
        //        if (file.Exists && !IsOverWrite) { return true; } else { file.Delete(); }

        //        var context = string.Empty;
        //        if (IsAppendTitle)
        //        {
        //            context += GetCsvRow(dt.GetColumnNames().ToArray());
        //            DoIOFile.Write(file.FullName, context, true, "UTF-8");
        //            context = string.Empty;
        //        }

        //        for (int i = 0; i < dt.Rows.Count; i++)
        //        {
        //            var row = dt.Rows[i];
        //            context += GetCsvRow(row.ItemArray) + "\n";

        //            if (context.Length >= 5000 || i == dt.Rows.Count - 1)
        //            {
        //                DoIOFile.Write(file.FullName, context, true, "UTF-8");
        //                context = string.Empty;
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex);
        //        return false;
        //    }
        //    return true;
        //}
        //#endregion

        //#region 将数组内容组合为双引号包裹的CSV行数据 

        ///// <summary>
        ///// 将数组内容组合为双引号包裹的CSV行数据
        ///// </summary>
        ///// <param name="dataItems"></param>
        ///// <returns></returns>
        //static string GetCsvRow(object[] dataItems)
        //{
        //    return string.Format("\"{0}\"", string.Join("\",\"", dataItems));
        //} 

        //#endregion

        #endregion
    }
}
